using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using ETL.Common.Logging;
using ETL.Entity;

namespace ETL.DataAccess
{
    public class ProjectRepository
    {
        public PROJECT GetProjectByProjectId(decimal projectId)
        {
            if (projectId > 0)
            {
                var db = new ETLDataEntities();
                var result = from s in db.PROJECTS 
                             where s.STATUS != "DRAFT"
                             orderby s.PROJECT_ID select s;
                if (result != null)
                {
                    return result.First();
                }
                else
                {
                    return null;
                }
               
            }
            else return null;
        }

        public IEnumerable<PROJECT> GetProjects()
        {
            var db = new ETLDataEntities();
            var result = from s in db.PROJECTS
                            orderby s.PROJECT_ID
                            select s;
            if (result != null)
            {
                return result;
            }
            else
            {
                return null;
            }

        }

        public IEnumerable<PROJECT> GetProjectsByUser(string userId)
        {
           //select prj.project_name
            //, tsk.task_name
            //, rin.resource_name
            //from projects prj,
            //tasks tsk,
            //scheduled_resource_usages sch,
            //resource_instances rin
            //where prj.project_id = tsk.gen_project_id
            //and tsk.gen_task_id = sch.gen_task_id
            //and sch.gen_resource_id = rin.gen_resource_id 
            
            var db = new ETLDataEntities();

            var result = from p in db.PROJECTS
                         join t in db.TASKS on p.PROJECT_ID equals t.GEN_PROJECT_ID
                         join s in db.SCHEDULED_RESOURCE_USAGES on t.GEN_TASK_ID equals s.GEN_TASK_ID
                         join r in db.RESOURCE_INSTANCES on s.GEN_RESOURCE_ID equals r.GEN_RESOURCE_ID     
                         where r.RESOURCE_NAME.ToUpper().Equals(userId.ToUpper())
                         orderby p.PROJECT_ID
                         select p;

            return result.Distinct();

        }

        public int UpdateProject(PROJECT project)
        {
            try
            {
                var db = new ETLDataEntities();
                decimal pid = project.PROJECT_ID;
                var thisProject = (from p in db.PROJECTS where p.PROJECT_ID == pid select p).FirstOrDefault();
                if (thisProject != null)
                {
                    thisProject.LEAD_ENGINEER_EMP_ID = project.LEAD_ENGINEER_EMP_ID;
                    db.Entry(thisProject).State = EntityState.Modified;
                    int i = db.SaveChanges();
                    return i;
                }
                return 0;
            }
            catch (Exception ex)
            {
                LogService.Service.Error(ex);
                return 0;
            }
        }
    }
}

